package com.gxuwz.attend.dao;

import com.gxuwz.attend.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 访问用户数据DAO类
 */
public class UserDao {
    /**
     * 插入
     * @param entity -值对象（Vo），封装包含成为实体对象的属性{encode}。
     * @param conn -连接对象，在Service组件的成员方法中创建。
     * @throws SQLException
     */
    public void insert(User entity, Connection conn)throws SQLException{
        StringBuffer sqlBuffer=new StringBuffer();
        sqlBuffer.append("insert into user(userid,fullname,password,status,email)values(?,?,?,?,?)");
        PreparedStatement pStmt =null;
        int index=1;
        try{
            pStmt =conn.prepareStatement(sqlBuffer.toString());
            //设置占位符?的值
            pStmt.setString(index++,entity.getUserid());
            pStmt.setString(index++,entity.getFullname());
            pStmt.setString(index++,entity.getPassword());
            pStmt.setString(index++,entity.getStatus());
            pStmt.setString(index++,entity.getEmail());
            //执行
            int c=pStmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            throw  e;
        }finally {
            //关闭pStmt对象
            pStmt.close();
        }
    }
    /**
     * 更新
     * @param entity -值对象（Vo），封装成实体对象的信息。
     * @param conn -连接对象，在Service组件的成员方法中创建。
     * @throws SQLException
     */
    public void update(User entity, Connection conn)throws SQLException{
        StringBuffer sqlBuffer=new StringBuffer();
        sqlBuffer.append("update user set fullname=?,password=? where userid=?");
        PreparedStatement pStmt =null;
        int index=1;
        try{
            pStmt =conn.prepareStatement(sqlBuffer.toString());
            //设置占位符?的值
            pStmt.setString(index++,entity.getFullname());
            pStmt.setString(index++,entity.getPassword());
            pStmt.setString(index++,entity.getUserid());
            //执行
            int c=pStmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            throw  e;
        }finally {
            //关闭pStmt对象
            if(pStmt!=null){
                pStmt.close();
            }
        }
    }
    /**
     *
     * @param id
     * @param conn
     * @throws SQLException
     */
    public void delete(String id, Connection conn)throws SQLException{
        StringBuffer sqlBuffer=new StringBuffer();
        sqlBuffer.append("delete from user where userid=?");
        PreparedStatement pStmt =null;
        int index=1;
        try{
            pStmt =conn.prepareStatement(sqlBuffer.toString());
            //设置占位符?的值
            pStmt.setString(index++,id);
            //执行
            int c=pStmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            throw  e;
        }finally {
            //关闭pStmt对象
            if(pStmt!=null){
                pStmt.close();
            }
        }
    }

    /**
     *
     * @param example
     * @return
     * @throws SQLException
     */
    public List<User> query(User example,Connection conn)throws SQLException{
        PreparedStatement pStmt =null;
        ResultSet rs =null;
        StringBuffer sqlBuffer=new StringBuffer();
        sqlBuffer.append("select * from user where 1=1 ");
        List<Object> paramList =new ArrayList<>();
        List<User>  results =new ArrayList<>();
        int parameterIndex=1;
        if(example!=null){
             //要注意增加过滤造成SQL注入攻击的非法字符的处理
             if (example.getUserid()!=null){
                 sqlBuffer.append(" and userid=?");
                 paramList.add(example.getUserid());
             }
            if (example.getPassword()!=null){
                sqlBuffer.append(" and password=?");
                paramList.add(example.getPassword());
            }
            if (example.getFullname()!=null){
                sqlBuffer.append(" and fullname like ?");
                paramList.add(example.getFullname()+"%");
            }
        }
        try{
            pStmt =conn.prepareStatement(sqlBuffer.toString());
            //设置占位符？对应的参数
            for (int i=0;i<paramList.size();i++) {
                Object param =paramList.get(i);
                if (param instanceof String){
                    pStmt.setString(parameterIndex++,(String)param);
                }
                if (param instanceof Integer){
                    pStmt.setInt(parameterIndex++,(Integer)param);
                }
                if (param instanceof Float){
                    pStmt.setFloat(parameterIndex++,(Float)param);
                }
            }
            //执行查询
            rs = pStmt.executeQuery();
            //遍历结果集
            if (rs!=null){
                while (rs.next()) {
                    String userid = rs.getString("userid");
                    String fullname = rs.getString("fullname");
                    String password = rs.getString("password");
                    String status = rs.getString("status");
                    String email = rs.getString("email");
                    User entity = new User();
                    entity.setUserid(userid);
                    entity.setFullname(fullname);
                    entity.setPassword(password);
                    entity.setStatus(status);
                    entity.setEmail(email);
                    results.add(entity);
                }
            }
            return results;
        }catch (SQLException e){
            e.printStackTrace();
            throw e;
        }finally {
            pStmt.close();
        }
    }

}
